package com.mbc.rmt.dao;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import com.mbc.common.dao.DAO;
import com.mbc.rmt.data.OpeningAgentDataList;
import com.mbc.rmt.entities.AgentBalance;
import com.mbc.rmt.entities.OpeningAgent;
import com.mbc.rmt.entities.OpeningAgentRpt;

public class OpeningAgentDAOImpls extends DAO<OpeningAgent> implements
		OpeningAgentDAO {

	public OpeningAgentDAOImpls(Connection conn) {
		this.conn = conn;
		this.table_name = "OpeningAgent";
	}

	@Override
	public void create(OpeningAgent entity) throws SQLException {
		query = "insert into "
				+ table_name
				+ " ( invoice_Id, invoice_Code, openingagentDate, agent_Id, currency_Id, currency_rate_Id, currency_rate_Code, rate, amount, branchId, userId) values( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
		pstmt = conn.prepareStatement(query);
		int i = 1;
		pstmt.setLong(i++, entity.getInvoice_Id());
		pstmt.setString(i++, entity.getInvoice_Code());
		pstmt.setDate(i++, new java.sql.Date(entity.getOpeningagentDate()
				.getTime()));
		pstmt.setLong(i++, entity.getAgent_Id());
		pstmt.setLong(i++, entity.getCurrency_Id());
		pstmt.setLong(i++, entity.getCurrency_rate_Id());
		pstmt.setString(i++, entity.getCurrency_rate_Code());
		pstmt.setDouble(i++, entity.getRate());
		pstmt.setDouble(i++, entity.getAmount());
		pstmt.setLong(i++, entity.getBranchId());
		pstmt.setLong(i++, entity.getUserId());
		pstmt.executeUpdate();
		pstmt.close();
	}

	@Override
	public void update(OpeningAgent entity) throws SQLException {
		query = "update "
				+ table_name
				+ " set invoice_Code = ?, openingagentDate = ?, agent_Id = ?, currency_Id = ?, currency_rate_Id = ?, currency_rate_Code = ?, rate = ?, amount = ?, branchId = ?, userId = ?"
				+ " where invoice_Id = " + entity.getInvoice_Id();
		pstmt = conn.prepareStatement(query);
		int i = 1;
		pstmt.setString(i++, entity.getInvoice_Code());
		pstmt.setDate(i++, new java.sql.Date(entity.getOpeningagentDate()
				.getTime()));
		pstmt.setLong(i++, entity.getAgent_Id());
		pstmt.setLong(i++, entity.getCurrency_Id());
		pstmt.setLong(i++, entity.getCurrency_rate_Id());
		pstmt.setString(i++, entity.getCurrency_rate_Code());
		pstmt.setDouble(i++, entity.getRate());
		pstmt.setDouble(i++, entity.getAmount());
		pstmt.setLong(i++, entity.getBranchId());
		pstmt.setLong(i++, entity.getUserId());
		pstmt.executeUpdate();
		pstmt.close();
	}

	@Override
	public void delete(long key) throws SQLException {
		query = getDeleteQuery() + " where invoice_Id = " + key;
		pstmt = conn.prepareStatement(query);
		pstmt.executeUpdate();
		pstmt.close();

	}

	@Override
	public OpeningAgent find(long key) throws SQLException {
		OpeningAgent l_OpeningAgent = null;
		query = getSelectQuery() + " where invoice_Id = " + key;
		pstmt = conn.prepareStatement(query);
		rs = pstmt.executeQuery();
		if (rs.next())
			l_OpeningAgent = readRecord(rs);
		rs.close();
		pstmt.close();
		return l_OpeningAgent;
	}

	@Override
	public OpeningAgent findByCode(String code,long branchId) throws SQLException {
		OpeningAgent l_OpeningAgent = null;
		query = " DECLARE @homecurrencyid bigint " +
				" SET @homecurrencyid=(SELECT HomeCurrencyid FROM Setup) "+
				" select oa.invoice_Id,oa.invoice_Code,oa.openingagentDate,"
				+ "oa.agent_Id,a.agent_code,a.name, "
				+ "oa.currency_Id,c.CurrencyCode,isnull(cr.currencyrateId,0)currency_rate_id, "
				+ " (CASE WHEN cr.currencyrateCode IS NULL AND @homecurrencyid=oa.currency_Id " 
				+ " THEN c.CurrencyCode+c.CurrencyCode ELSE cr.currencyrateCode END )currency_rate_code, " 
				+ " oa.rate,oa.amount,oa.branchId from OpeningAgent oa "
				+ "left join Agent a on a.agent_id = oa.agent_Id "
				+ "left join Currency c on c.CurrencyId = oa.currency_Id "
				+ "left join CurrencyRate cr on cr.SourceCurrencyId=oa.currency_Id"
				+ " and cr.ConvertCurrencyId=@homecurrencyid " 
				+ " and cr.BranchId= oa.BranchId "
				+ " where invoice_Code = '" + code 
				+ "' and oa.BranchId = " + branchId;
		pstmt = conn.prepareStatement(query);
		rs = pstmt.executeQuery();
		if (rs.next())
			l_OpeningAgent = readRecords(rs);
		rs.close();
		pstmt.close();
		return l_OpeningAgent;
	}

	@Override
	public ArrayList<OpeningAgent> findAll() throws SQLException {
		ArrayList<OpeningAgent> l_OpeningAgentList = new ArrayList<OpeningAgent>();
		query = getSelectQuery();
		pstmt = conn.prepareStatement(query);
		rs = pstmt.executeQuery();
		while (rs.next()) {
			l_OpeningAgentList.add(readRecord(rs));
		}
		rs.close();
		pstmt.close();
		return l_OpeningAgentList;
	}

	@Override
	public OpeningAgentDataList findOpeningAgents(OpeningAgent cri)
			throws SQLException {
		OpeningAgentDataList l_DataList = new OpeningAgentDataList();
		ArrayList<OpeningAgent> l_entityList = new ArrayList<OpeningAgent>();
		String l_cri = "";
		if(!cri.getFieldname().equals("oa.openingagentdate"))
			l_cri += " and " + cri.getFieldname() + " like N'%" + cri.getKeyword() + "%'";
//				" and (invoice_Code like N'%" + cri.getKeyword() + "%')"
		else
			l_cri += " and (Convert(varchar(100)," + cri.getFieldname() + ", 103)) like N'%" + cri.getKeyword() + "%'";
		l_cri += " and oa.BranchId = " + cri.getBranchId();
		query =  " DECLARE @homecurrencyid bigint " 
				+ " SET @homecurrencyid=(SELECT HomeCurrencyid FROM Setup)"
				+ "set rowcount "
				+ cri.getSize()
				+ " select ### from (select row_number() over(order by invoice_Id desc) as [rowno], oa.invoice_Id,oa.invoice_Code,oa.openingagentDate,"
				+ "oa.agent_Id,a.agent_code,a.name, "
				+ "oa.currency_Id,c.CurrencyCode,isnull(cr.currencyrateId,0)currency_rate_id,"
				+ "(CASE WHEN cr.currencyrateCode IS NULL AND @homecurrencyid=oa.currency_id"
				+ " THEN c.currencycode+c.currencycode ELSE cr.currencyratecode END )currency_rate_code, "
				+ " oa.rate,oa.amount,oa.branchId from OpeningAgent oa "
				+ "left join Agent a on a.agent_id = oa.agent_Id "
				+ "left join Currency c on c.CurrencyId = oa.currency_Id "
				+ "left join CurrencyRate cr on cr.sourcecurrencyid=oa.currency_Id "
				+ " and cr.convertcurrencyid=@homecurrencyid "
				+ " and cr.BranchId= oa.BranchId "
				+ " where 1 = 1" + l_cri + ") " + table_name;
		
		String l_query = query.replace("###", "*");
		l_query += " where rowno > " + cri.getIndex();
		pstmt = conn.prepareStatement(l_query);
		rs = pstmt.executeQuery();
		if (rs.next()) {
			do {
				l_entityList.add(readRecords(rs));
			} while (rs.next());
		}
		l_DataList.setEntityList(l_entityList);
		rs.close();
		pstmt.close();
		//String l_cri1 = " and (invoice_Code like N'%" + cri.getKeyword()
				//+ "%')" + " and BranchId = " + cri.getBranchId();
		//query = "select count(invoice_Id) from " + table_name + " where 1 = 1"
				//+ l_cri1;
		String l_query2 = query.replace("###", "count(*)");
		pstmt = conn.prepareStatement(l_query2);
		rs = pstmt.executeQuery();
		if (rs.next()) {
			l_DataList.setTotalRecord(rs.getLong(1));
		}
		rs.close();
		pstmt.close();
		return l_DataList;
	}

	@Override
	protected OpeningAgent readRecord(ResultSet rs) throws SQLException {
		OpeningAgent l_entity = new OpeningAgent();
		l_entity.setInvoice_Id(rs.getLong("invoice_Id"));
		l_entity.setInvoice_Code(rs.getString("invoice_Code"));
		l_entity.setOpeningagentDate(rs.getDate("openingagentDate"));
		l_entity.setAgent_Id(rs.getLong("agent_id"));
		l_entity.setCurrency_Id(rs.getLong("currency_id"));
		l_entity.setCurrency_rate_Id(rs.getLong("currency_rate_id"));
		l_entity.setCurrency_rate_Code(rs.getString("currency_rate_code"));
		l_entity.setRate(rs.getDouble("rate"));
		l_entity.setAmount(rs.getDouble("amount"));
		l_entity.setBranchId(rs.getLong("BranchId"));
		l_entity.setUserId(rs.getLong("userId"));
		return l_entity;
	}

	@Override
	public OpeningAgent getOpeningAgent(long key) throws SQLException {
		OpeningAgent l_OpeningAgent = null;
		query = "select oa.invoice_Id,oa.invoice_Code,oa.date,"
				+ "oa.agent_Id,a.agent_code,a.name,"
				+ "oa.currency_Id,c.CurrencyCode,oa.currency_rate_Id,"
				+ "oa.currency_rate_Code,oa.rate,"
				+ "oa.amount,oa.branchId from OpeningAgent oa "
				+ "left join Agent a on a.agent_id = oa.agent_Id"
				+ "left join Currency c on c.CurrencyId = oa.currency_Id "
				+ "left join CurrencyRate cr on cr.CurrencyRateId=oa.currency_rate_Id"
				+ " where invoice_Id = " + key;
		pstmt = conn.prepareStatement(query);
		rs = pstmt.executeQuery();
		if (rs.next())
			l_OpeningAgent = readRecords(rs);
		rs.close();
		pstmt.close();
		return l_OpeningAgent;
	}

	protected OpeningAgent readRecords(ResultSet rs) throws SQLException {
		OpeningAgent l_entity = new OpeningAgent();
		l_entity.setInvoice_Id(rs.getLong("invoice_Id"));
		l_entity.setInvoice_Code(rs.getString("invoice_Code"));
		l_entity.setOpeningagentDate(rs.getDate("openingagentDate"));
		l_entity.setAgent_Id(rs.getLong("agent_id"));
		l_entity.setAgent_Code(rs.getString("agent_code"));
		l_entity.setAgent_name(rs.getString("name"));
		l_entity.setCurrency_Id(rs.getLong("currency_id"));
		l_entity.setCurrency_Code(rs.getString("currencyCode"));
		l_entity.setCurrency_rate_Id(rs.getLong("currency_rate_id"));
		l_entity.setCurrency_rate_Code(rs.getString("currency_rate_code"));
		l_entity.setRate(rs.getDouble("rate"));
		l_entity.setAmount(rs.getDouble("amount"));
		l_entity.setBranchId(rs.getLong("BranchId"));
		return l_entity;
	}

	@Override
	public ArrayList<AgentBalance> getAgentBalance(long agent_id)
			throws SQLException {
		ArrayList<AgentBalance> l_AgentBalanceList = new ArrayList<AgentBalance>();
		query = " select ab.agentbalance_Id,ab.agent_Id,ab.currencyId,"
				+ " ab.OpeningBalance,ab.CurrentBalance "
				+ " from OpeningAgent oa "
				+ " inner join AgentBalance ab on oa.agent_Id=ab.agent_Id and oa.currency_Id=ab.currencyId "
				+ " where oa.agent_Id = " + agent_id;
		pstmt = conn.prepareStatement(query);
		rs = pstmt.executeQuery();
		if (rs.next()) {
			do {

				l_AgentBalanceList.add(readRecordAB(rs));
			} while (rs.next());
		}
		rs.close();
		pstmt.close();
		return l_AgentBalanceList;
	}

	protected AgentBalance readRecordAB(ResultSet rs) throws SQLException {
		AgentBalance l_entity = new AgentBalance();
		l_entity.setAgentbalance_id(rs.getLong("agentbalance_id"));
		l_entity.setAgent_id(rs.getLong("agent_id"));
		l_entity.setCurrencyid(rs.getLong("currencyid"));
		l_entity.setOpeningbalance(rs.getDouble("openingbalance"));
		l_entity.setCurrentbalance(rs.getDouble("currentbalance"));
		return l_entity;
	}

	@Override
	public ResultSet prepareOpeningAgentReport(OpeningAgentRpt info)
			throws SQLException {
		ResultSet l_rs;
		String l_query = "dbo.sp_cr_OpeningAgentRpt ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?";

		CallableStatement l_pstmt = conn.prepareCall(l_query);
		OpeningAgentRptParameter(l_pstmt, info);
		l_rs = l_pstmt.executeQuery();
		return l_rs;
	}

	private static void OpeningAgentRptParameter(CallableStatement l_pstmt,
			OpeningAgentRpt info) throws SQLException {
		int i = 1;

		l_pstmt.setString(i++, info.getFromDate());
		l_pstmt.setString(i++, info.getToDate());
		l_pstmt.setString(i++, info.getFromInvoiceID());
		l_pstmt.setString(i++, info.getToInvoiceID());
		l_pstmt.setString(i++, info.getFromAgentID());
		l_pstmt.setString(i++, info.getToAgentID());
		l_pstmt.setString(i++, info.getFromUserID());
		l_pstmt.setString(i++, info.getToUserID());
		l_pstmt.setString(i++, info.getFromCurrencyID());
		l_pstmt.setString(i++, info.getToCurrencyID());
		l_pstmt.setString(i++, info.getOrderby());
		l_pstmt.setLong(i++, info.getBranchId());
		

	}
}
